Pip install the non-common modules

!pip install -U kaleido   # kaleido required for saving the plotly figures into static images

Collecting kaleido
  Downloading kaleido-0.2.1-py2.py3-none-macosx_10_11_x86_64.whl (85.2 MB)
     |████████████████████████████████| 85.2 MB 276 kB/s 
Installing collected packages: kaleido
Successfully installed kaleido-0.2.1

!pip install geopandas --quiet
!pip install geoplot --quiet

!pip install watermark
%load_ext watermark
%watermark -d -m -v -p numpy,matplotlib,sklearn,pandas

Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/
Collecting watermark
  Downloading watermark-2.3.1-py2.py3-none-any.whl (7.2 kB)
Requirement already satisfied: ipython in /usr/local/lib/python3.7/dist-packages (from watermark) (5.5.0)
Requirement already satisfied: importlib-metadata>=1.4 in /usr/local/lib/python3.7/dist-packages (from watermark) (4.11.4)
Requirement already satisfied: zipp>=0.5 in /usr/local/lib/python3.7/dist-packages (from importlib-metadata>=1.4->watermark) (3.8.0)
Requirement already satisfied: typing-extensions>=3.6.4 in /usr/local/lib/python3.7/dist-packages (from importlib-metadata>=1.4->watermark) (4.1.1)
Requirement already satisfied: decorator in /usr/local/lib/python3.7/dist-packages (from ipython->watermark) (4.4.2)
Requirement already satisfied: traitlets>=4.2 in /usr/local/lib/python3.7/dist-packages (from ipython->watermark) (5.1.1)
Requirement already satisfied: pygments in /usr/local/lib/python3.7/dist-packages (from ipython->watermark) (2.6.1)
Requirement already satisfied: prompt-toolkit<2.0.0,>=1.0.4 in /usr/local/lib/python3.7/dist-packages (from ipython->watermark) (1.0.18)
Requirement already satisfied: simplegeneric>0.8 in /usr/local/lib/python3.7/dist-packages (from ipython->watermark) (0.8.1)
Requirement already satisfied: pickleshare in /usr/local/lib/python3.7/dist-packages (from ipython->watermark) (0.7.5)
Requirement already satisfied: setuptools>=18.5 in /usr/local/lib/python3.7/dist-packages (from ipython->watermark) (57.4.0)
Requirement already satisfied: pexpect in /usr/local/lib/python3.7/dist-packages (from ipython->watermark) (4.8.0)
Requirement already satisfied: wcwidth in /usr/local/lib/python3.7/dist-packages (from prompt-toolkit<2.0.0,>=1.0.4->ipython->watermark) (0.2.5)
Requirement already satisfied: six>=1.9.0 in /usr/local/lib/python3.7/dist-packages (from prompt-toolkit<2.0.0,>=1.0.4->ipython->watermark) (1.15.0)
Requirement already satisfied: ptyprocess>=0.5 in /usr/local/lib/python3.7/dist-packages (from pexpect->ipython->watermark) (0.7.0)
Installing collected packages: watermark
Successfully installed watermark-2.3.1
Python implementation: CPython
Python version       : 3.7.13
IPython version      : 5.5.0

numpy     : 1.21.6
matplotlib: 3.2.2
sklearn   : 0.0
pandas    : 1.3.5

Compiler    : GCC 7.5.0
OS          : Linux
Release     : 5.4.188+
Machine     : x86_64
Processor   : x86_64
CPU cores   : 2
Architecture: 64bit

Set up

#@title ## Base imports
import os
import sys
import numpy as np
import scipy
import sklearn
import sklearn.linear_model
import pandas as pd
import plotly.express as px

#@title ## Option 1) Mount google drive and import my code

mountpoint_folder_name = "gdrive"  # can be anything, doesn't have to be "drive"
project_path_within_drive = "PythonProjects/GeospatialAnalysis" #@param {type:"string"}
project_path_full = os.path.join("/content/",mountpoint_folder_name,
                        "MyDrive",project_path_within_drive)
try:
    import google.colab.drive
    import os, sys
    # Need to move out of google drive directory if going to remount
    %cd
    # drive.mount documentation can be accessed via: drive.mount?
    #Signature: drive.mount(mountpoint, force_remount=False, timeout_ms=120000, use_metadata_server=False)
    google.colab.drive.mount(os.path.join("/content/",mountpoint_folder_name), force_remount=True)  # mounts to a folder called mountpoint_folder_name

    if project_path_full not in sys.path:
        pass
        #sys.path.insert(0,project_path_full)
    %cd {project_path_full}
    
except ModuleNotFoundError:  # in case not run in Google colab
    import traceback
    traceback.print_exc()

/root
Mounted at /content/gdrive
/content/gdrive/.shortcut-targets-by-id/1okL5s1HTQUWqKodPSVNP_RsK0o81IH5E/PythonProjects/GeospatialAnalysis

#@title ## Option 2) Clone project files from Github

!git clone https://github.com/ryerrabelli/GeospatialAnalysis.git

project_path_full = os.path.join("/content/","GeospatialAnalysis")
sys.path.insert(1,project_path_full)
%cd GeospatialAnalysis
print(sys.path)

Cloning into 'GeospatialAnalysis'...
remote: Enumerating objects: 58, done.
remote: Counting objects: 100% (58/58), done.
remote: Compressing objects: 100% (43/43), done.
remote: Total 58 (delta 15), reused 54 (delta 11), pack-reused 0
Unpacking objects: 100% (58/58), done.
['', '/content/GeospatialAnalysis', '/content', '/env/python', '/usr/lib/python37.zip', '/usr/lib/python3.7', '/usr/lib/python3.7/lib-dynload', '/usr/local/lib/python3.7/dist-packages', '/usr/lib/python3/dist-packages', '/usr/local/lib/python3.7/dist-packages/IPython/extensions', '/root/.ipython']

Set up a folder for saving images

image_folder_path = "outputs"
if not os.path.exists(image_folder_path):
    os.mkdir(image_folder_path)

def save_figure(fig, file_name:str, animated=False):
    """
    fig is of type plotly.graph_objs._figure.Figure,
    Requires kaleido to be installed
    """
    fig.write_html(os.path.join(image_folder_path, file_name+".html"))
    if not animated:
        fig.write_image(os.path.join(image_folder_path, file_name+".svg"))
        fig.write_image(os.path.join(image_folder_path, file_name+".png"))
        fig.write_image(os.path.join(image_folder_path, file_name+".jpg"))

Load data

Load geojson data

from urllib.request import urlopen
import json
with urlopen('https://raw.githubusercontent.com/plotly/datasets/master/geojson-counties-fips.json') as response:
    counties = json.load(response)

for k,v in counties.items():
    print(k,": ",v)

IOPub data rate exceeded.
The notebook server will temporarily stop sending output
to the client in order to avoid crashing it.
To change this limit, set the config variable
`--NotebookApp.iopub_data_rate_limit`.

Current values:
NotebookApp.iopub_data_rate_limit=1000000.0 (bytes/sec)
NotebookApp.rate_limit_window=3.0 (secs)

Test

dfA = pd.read_csv("data/2022_04_10 ent initial output.csv", dtype={"FIPS": str})  # gets per county info
dfB = pd.read_csv("data/2022_05_05 sums and slopes ent.csv", dtype={"HCPCS Code": str})  # gets per healthcare code info

display(dfA.info())
display(dfB.info())

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 941 entries, 0 to 940
Data columns (total 72 columns):
 #   Column                                              Non-Null Count  Dtype  
---  ------                                              --------------  -----  
 0   FIPS                                                941 non-null    object 
 1   Total Number of Services                            941 non-null    float64
 2   Total Medicare Payment Amount                       941 non-null    float64
 3   Total Number of Services: 2019                      941 non-null    float64
 4   Total Medicare Payment Amount: 2019                 941 non-null    float64
 5   Total Number of Services: 2018                      941 non-null    float64
 6   Total Medicare Payment Amount: 2018                 941 non-null    float64
 7   Total Number of Services: 2017                      941 non-null    float64
 8   Total Medicare Payment Amount: 2017                 941 non-null    float64
 9   Total Number of Services: 2016                      941 non-null    float64
 10  Total Medicare Payment Amount: 2016                 941 non-null    float64
 11  Total Number of Services: 2015                      941 non-null    float64
 12  Total Medicare Payment Amount: 2015                 941 non-null    float64
 13  tot_ratio                                           941 non-null    float64
 14  % ASC Procedures: 2019                              941 non-null    float64
 15  % ASC Billing: 2019                                 941 non-null    float64
 16  % ASC Procedures: 2018                              941 non-null    float64
 17  % ASC Billing: 2018                                 941 non-null    float64
 18  % ASC Procedures: 2017                              941 non-null    float64
 19  % ASC Billing: 2017                                 941 non-null    float64
 20  % ASC Procedures: 2016                              941 non-null    float64
 21  % ASC Billing: 2016                                 941 non-null    float64
 22  % ASC Procedures: 2015                              941 non-null    float64
 23  % ASC Billing: 2015                                 941 non-null    float64
 24  % ASC Procedures                                    941 non-null    float64
 25  % ASC Billing                                       941 non-null    float64
 26  Beneficiaries with Part A and Part B                941 non-null    float64
 27  Average Age                                         941 non-null    float64
 28  Percent Male                                        941 non-null    float64
 29  Percent Non-Hispanic White                          941 non-null    float64
 30  Percent African American                            941 non-null    float64
 31  Percent Hispanic                                    941 non-null    float64
 32  Percent Eligible for Medicaid                       941 non-null    float64
 33  Average HCC Score                                   941 non-null    float64
 34  Hospital Readmission Rate                           941 non-null    float64
 35  Emergency Department Visits per 1000 Beneficiaries  941 non-null    float64
 36  Procedures Per Capita Standardized Costs            941 non-null    float64
 37  Procedure Events Per 1000 Beneficiaries             941 non-null    float64
 38  metro                                               941 non-null    int64  
 39  pct_poverty                                         941 non-null    float64
 40  median_house_income                                 941 non-null    float64
 41  pop                                                 941 non-null    float64
 42  2013_Rural_urban_cont_code                          941 non-null    float64
 43  Pct_wthout_high_diploma                             941 non-null    float64
 44  Pct_wth_high_diploma                                941 non-null    float64
 45  Pct_wth_some_coll                                   941 non-null    float64
 46  Pct_wth_coll_degree                                 941 non-null    float64
 47  unemployment                                        941 non-null    float64
 48  pct_uninsured                                       941 non-null    float64
 49  fibro                                               941 non-null    float64
 50  tabacco                                             941 non-null    float64
 51  obesity                                             941 non-null    float64
 52  migrane                                             941 non-null    float64
 53  Alzheimers                                          941 non-null    float64
 54  Depression                                          941 non-null    float64
 55  Alcohol Abuse                                       941 non-null    float64
 56  Drug Abuse                                          941 non-null    float64
 57  Schizo_othr_psych                                   941 non-null    float64
 58  COPD                                                941 non-null    float64
 59  Chronic Kidney Disease                              941 non-null    float64
 60  Osteoporosis                                        941 non-null    float64
 61  Stroke                                              941 non-null    float64
 62  Diabetes                                            941 non-null    float64
 63  Asthma                                              941 non-null    float64
 64  Arthritis                                           941 non-null    float64
 65  Hypertension                                        941 non-null    float64
 66  Heart Failure                                       941 non-null    float64
 67  Ischemic Heart Disease                              941 non-null    float64
 68  Population Density                                  941 non-null    float64
 69  Medicare Population Density                         941 non-null    float64
 70  Moran I score for ACS billing fraction              941 non-null    object 
 71  County                                              941 non-null    object 
dtypes: float64(68), int64(1), object(3)
memory usage: 529.4+ KB

None

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 53 entries, 0 to 52
Data columns (total 17 columns):
 #   Column                                       Non-Null Count  Dtype  
---  ------                                       --------------  -----  
 0   HCPCS Code                                   53 non-null     int64  
 1   Total Number of Services                     53 non-null     float64
 2   Total Medicare Payment Amount                53 non-null     float64
 3   Total Number of Services: 2019               53 non-null     float64
 4   Total Medicare Payment Amount: 2019          53 non-null     float64
 5   Total Number of Services: 2018               53 non-null     float64
 6   Total Medicare Payment Amount: 2018          53 non-null     float64
 7   Total Number of Services: 2017               53 non-null     float64
 8   Total Medicare Payment Amount: 2017          53 non-null     float64
 9   Total Number of Services: 2016               53 non-null     float64
 10  Total Medicare Payment Amount: 2016          53 non-null     float64
 11  Total Number of Services: 2015               53 non-null     float64
 12  Total Medicare Payment Amount: 2015          53 non-null     float64
 13  Total Medicare Payment Amount: Slope         53 non-null     float64
 14  Total Medicare Payment Amount: Pearson Coef  53 non-null     float64
 15  Total Number of Services: Slope              53 non-null     float64
 16  Total Number of Services: Pearson Coef       53 non-null     float64
dtypes: float64(16), int64(1)
memory usage: 7.2 KB

None

dfB["HCPCS Code"]

0     11042
1     11642
2     11643
3     14040
4     14041
5     14060
6     14301
7     15004
8     15120
9     15260
10    15730
11    20926
12    21235
13    30130
14    30140
15    30465
16    30520
17    30802
18    30930
19    31231
20    31237
21    31238
22    31240
23    31253
24    31254
25    31256
26    31257
27    31259
28    31267
29    31276
30    31288
31    31525
32    31526
33    31535
34    31536
35    31541
36    31571
37    31575
38    31579
39    31622
40    38510
41    42415
42    42826
43    43191
44    43200
45    60220
46    60240
47    60500
48    69433
49    69436
50    69631
51    69801
52    69930
Name: HCPCS Code, dtype: int64

for ind in range(max(len(dfA.columns),len(dfB.columns))):
    dfAstr = str(dfA.columns[ind])
    print(dfAstr, " "*(60-len(dfAstr)),
          (dfB.columns[ind] if ind < len(dfB.columns) else "x" ))

FIPS                                                          HCPCS Code
Total Number of Services                                      Total Number of Services
Total Medicare Payment Amount                                 Total Medicare Payment Amount
Total Number of Services: 2019                                Total Number of Services: 2019
Total Medicare Payment Amount: 2019                           Total Medicare Payment Amount: 2019
Total Number of Services: 2018                                Total Number of Services: 2018
Total Medicare Payment Amount: 2018                           Total Medicare Payment Amount: 2018
Total Number of Services: 2017                                Total Number of Services: 2017
Total Medicare Payment Amount: 2017                           Total Medicare Payment Amount: 2017
Total Number of Services: 2016                                Total Number of Services: 2016
Total Medicare Payment Amount: 2016                           Total Medicare Payment Amount: 2016
Total Number of Services: 2015                                Total Number of Services: 2015
Total Medicare Payment Amount: 2015                           Total Medicare Payment Amount: 2015
tot_ratio                                                     Total Medicare Payment Amount: Slope
% ASC Procedures: 2019                                        Total Medicare Payment Amount: Pearson Coef
% ASC Billing: 2019                                           Total Number of Services: Slope
% ASC Procedures: 2018                                        Total Number of Services: Pearson Coef
% ASC Billing: 2018                                           x
% ASC Procedures: 2017                                        x
% ASC Billing: 2017                                           x
% ASC Procedures: 2016                                        x
% ASC Billing: 2016                                           x
% ASC Procedures: 2015                                        x
% ASC Billing: 2015                                           x
% ASC Procedures                                              x
% ASC Billing                                                 x
Beneficiaries with Part A and Part B                          x
Average Age                                                   x
Percent Male                                                  x
Percent Non-Hispanic White                                    x
Percent African American                                      x
Percent Hispanic                                              x
Percent Eligible for Medicaid                                 x
Average HCC Score                                             x
Hospital Readmission Rate                                     x
Emergency Department Visits per 1000 Beneficiaries            x
Procedures Per Capita Standardized Costs                      x
Procedure Events Per 1000 Beneficiaries                       x
metro                                                         x
pct_poverty                                                   x
median_house_income                                           x
pop                                                           x
2013_Rural_urban_cont_code                                    x
Pct_wthout_high_diploma                                       x
Pct_wth_high_diploma                                          x
Pct_wth_some_coll                                             x
Pct_wth_coll_degree                                           x
unemployment                                                  x
pct_uninsured                                                 x
fibro                                                         x
tabacco                                                       x
obesity                                                       x
migrane                                                       x
Alzheimers                                                    x
Depression                                                    x
Alcohol Abuse                                                 x
Drug Abuse                                                    x
Schizo_othr_psych                                             x
COPD                                                          x
Chronic Kidney Disease                                        x
Osteoporosis                                                  x
Stroke                                                        x
Diabetes                                                      x
Asthma                                                        x
Arthritis                                                     x
Hypertension                                                  x
Heart Failure                                                 x
Ischemic Heart Disease                                        x
Population Density                                            x
Medicare Population Density                                   x
Moran I score for ACS billing fraction                        x
County                                                        x

Load ENT county df (specifically wide-type df) from csv file

To understand what is meant by long type and wife type dataframes, see https://towardsdatascience.com/visualization-with-plotly-express-comprehensive-guide-eb5ee4b50b57

df_bill_orig = pd.read_csv("data/2022_05_05 sums and slopes ent with HCPCS descriptions.csv", 
                           dtype={
                               "HCPCS Code": str,
                               "Total Number of Services": np.int64,
                               **{f"Total Number of Services: {year}": np.int64 for year in range(2015,2019+1)}
                               })  # gets per healthcare code info

df_bill_orig.head(2)

	HCPCS Code	HCPCS Description	Total Number of Services	Total Medicare Payment Amount	Total Number of Services: 2019	Total Medicare Payment Amount: 2019	Total Number of Services: 2018	Total Medicare Payment Amount: 2018	Total Number of Services: 2017	Total Medicare Payment Amount: 2017	Total Number of Services: 2016	Total Medicare Payment Amount: 2016	Total Number of Services: 2015	Total Medicare Payment Amount: 2015	Total Medicare Payment Amount: Slope	Total Medicare Payment Amount: Pearson Coef	Total Number of Services: Slope	Total Number of Services: Pearson Coef
0	11042	Removal of skin and tissue first 20 sq cm or less	46073	4237372.20	6537	644733.10	11483	1317431.90	14608	1165644.85	8266	600263.11	5179	509299.24	0.000002	0.425707	0.000101	0.245111
1	11642	Removal of malignant growth (1.1 to 2.0 centim...	14749	1201135.31	2634	214496.56	2585	213254.24	2923	238474.17	3055	245483.24	3552	289427.10	-0.000047	-0.929111	-0.003798	-0.935792

df_bill_wide = df_bill_orig.set_index(["HCPCS Code", "HCPCS Description"])
# Rename the columns so they can be split  easier. The 20 is the first two digits of the year columns
df_bill_wide.columns = [col.replace(": ",": : ").replace(": 20","Annual: 20") for col in df_bill_wide.columns]
# Multiindex
df_bill_wide.columns = pd.MultiIndex.from_tuples([tuple(col.split(": ")) if ":" in col else (col,"","Sum") for col in df_bill_wide.columns], names=["Category","Stat","Year"])
df_bill_wide = df_bill_wide[sorted(df_bill_wide)]  # rearrange cols alphabetically
df_bill_wide = df_bill_wide.sort_values(by=("Total Number of Services","","Sum"), ascending=False)  # sort rows by volume 
categories = df_bill_wide.columns.levels[0]  #["Total Number of Services", "Total Medicare Payment Amount"]

The slope given in the csv file is actually the inverse slope. We need to either recalculate it or

def calc_slope(y, x):
    a = scipy.stats.linregress(x, y=y)
    return {"Slope":a.slope, "Pearson Coef":a.rvalue, "Intercept": a.intercept, "P":a.pvalue}

Index(['Total Medicare Payment Amount', 'Total Number of Services'], dtype='object', name='Category')

for category in categories:
    new_df = df_bill_wide[(category,"Annual")].apply(calc_slope,axis=1, result_type="expand", args=(np.arange(2015,2019+1),) )
    df_bill_wide[[(category,"",new_col) for new_col in new_df.columns ]]=new_df
    #df_bill_wide[(category,"","Slope")]=df_bill_wide[(category,"Annual")].apply(calc_slope,axis=1)
df_bill_wide = df_bill_wide[sorted(df_bill_wide.columns)]  # rearrange cols alphabetically

/var/folders/1h/c6vmx9jd2lg6rj_wkfdl22pr0000gn/T/ipykernel_62791/342045181.py:2: PerformanceWarning: indexing past lexsort depth may impact performance.
  new_df = df_bill_wide[(category,"Annual")].apply(calc_slope,axis=1, result_type="expand", args=(np.arange(2015,2019+1),) )

Nick suppl table

df_bill_wide.loc[["14060",
"69930",
"31267",
"30520",
"30140",
"15260",
"14301",
"14040",
"31575",
"31276",
"14041",
"60500",
"31231",
"31579",
"69436",
"31256",
"31525",
"11042",
"31237"]]

	Category	Total Medicare Payment Amount								Total Number of Services
	Year	2015	2016	2017	2018	2019	Pearson Coef	Slope	Total	2015	2016	2017	2018	2019	Pearson Coef	Slope	Total
HCPCS Code	HCPCS Description
14060	Tissue transfer repair of wound (10 sq centimeters or less) of eyelids, nose, ears, and/or lips	7253164.21	7562395.26	6718042.68	7190368.43	6664704.64	-0.644093	-2.680000e-06	35388675.22	13950	14043	13157	13033	12781	-0.931226	-0.002590	66964
69930	Implantation of cochlear device	4164557.97	4694007.58	6277113.34	8438188.15	9406753.83	0.983619	6.800000e-07	32980620.87	1626	1818	2191	2587	2677	0.984317	0.003375	10899
31267	Removal of nasal sinus tissue using an endoscope	2454520.71	4313264.94	4546272.86	5081500.91	5474723.78	0.923983	1.250000e-06	21870283.20	7561	8394	9166	9227	10126	0.976416	0.001599	44474
30520	Reshaping of nasal cartilage	3822391.75	3510467.18	3887689.27	4454847.45	4932682.76	0.882351	2.460000e-06	20608078.41	9409	9508	10270	10119	10934	0.933139	0.002378	50240
30140	Removal of nasal air passage	4090571.97	4163402.15	4804703.77	3414962.46	3699636.24	-0.459544	-1.380000e-06	20173276.59	9942	11063	12663	13489	14256	0.990276	0.000887	61413
15260	Relocation of patient skin to nose, ears, eyelids, and/or lips (20 sq centimeters or less)	3816163.30	4358891.55	3827316.88	3987570.86	3502561.56	-0.506593	-2.570000e-06	19492504.15	7255	7518	7164	6646	6439	-0.885195	-0.003129	35022
14301	Tissue transfer repair of wound (30.1 to 60.0 sq centimeters)	3422400.08	3325471.33	3811793.74	4247217.10	4435581.13	0.951445	3.070000e-06	19242463.38	4204	4516	4544	4910	5027	0.975466	0.004664	23201
14040	Tissue transfer repair of wound (10 sq centimeters or less) of the forehead, cheeks, chin, mouth, neck, underarms, genitals, hands, and/or feet	4175118.98	3907715.69	3725634.72	3471107.63	3582572.68	-0.921821	-5.240000e-06	18862149.70	8379	7635	7356	6475	6854	-0.907694	-0.001957	36699
31575	Diagnostic examination of voice box using flexible endoscope	4095655.56	3367114.38	2963951.91	3007322.23	3164731.95	-0.760895	-2.610000e-06	16598776.03	69604	58104	58140	59663	62112	-0.442025	-0.000146	307623
31276	Exploration of nasal sinus using an endoscope	3551553.36	3919275.81	4510942.92	1568753.14	1926164.94	-0.690081	-8.500000e-07	15476690.17	5855	6240	7061	3202	3838	-0.680085	-0.000654	26196
14041	Tissue transfer repair of wound (10.1 to 30.0 sq centimeters) of the forehead, cheeks, chin, mouth, neck, underarms, genitals, hands, and/or feet	2481173.43	2174377.77	2237142.30	2455397.22	2448822.04	0.240025	2.660000e-06	11796912.76	4486	3871	4066	4205	4311	-0.010797	-0.000073	20939
60500	Removal or exploration of parathyroid glands	1466292.92	1408770.07	1410847.34	1862490.74	2197618.10	0.863679	3.890000e-06	8346019.17	1960	1870	1798	2272	2644	0.800555	0.003621	10544
31231	Diagnostic examination of nasal passages using an endoscope	1561172.64	1473524.08	1495479.72	1726503.13	1865779.80	0.814438	7.690000e-06	8122459.37	33396	32245	32767	38601	42188	0.866909	0.000314	179197
31579	Examination to assess movement of vocal cord flaps using an endoscope	1329457.32	1080066.04	957141.66	1213382.40	1418448.74	0.264974	2.260000e-06	5998496.16	11995	9957	10470	13064	15023	0.708342	0.000548	60509
69436	Incision of eardrum with insertion of eardrum tube under general anesthesia	1291093.18	1541750.79	989017.79	1045676.06	1013430.98	-0.700318	-4.660000e-06	5880968.80	4371	4125	3889	4011	4032	-0.694376	-0.006088	20428
31256	Incision of nasal sinus using an endoscope	1021363.93	1035079.99	1059938.69	913926.80	991835.22	-0.506544	-1.420000e-05	5022144.63	3599	3847	3956	3242	3465	-0.479295	-0.002631	18109
31525	Diagnostic examination of voice box using an endoscope	548628.32	783501.65	700741.37	1022976.64	1268959.66	0.936517	5.220000e-06	4324807.64	4231	5918	5299	7722	9470	0.935413	0.000712	32640
11042	Removal of skin and tissue first 20 sq cm or less	509299.24	600263.11	1165644.85	1317431.90	644733.10	0.425707	1.830000e-06	4237372.20	5179	8266	14608	11483	6537	0.245111	0.000101	46073
31237	Biopsy or removal of nasal polyp or tissue using an endoscope	689656.89	700877.99	831306.55	817973.23	945048.46	0.943382	1.420000e-05	3984863.12	4011	3961	4811	4766	5431	0.935600	0.002402	22980

df_bill_wide.loc[["31575","31237"]]

	Category	Total Medicare Payment Amount								Total Number of Services
	Year	2015	2016	2017	2018	2019	Pearson Coef	Slope	Total	2015	2016	2017	2018	2019	Pearson Coef	Slope	Total
HCPCS Code	HCPCS Description
31575	Diagnostic examination of voice box using flexible endoscope	4095655.56	3367114.38	2963951.91	3007322.23	3164731.95	-0.760895	-0.000003	16598776.03	69604	58104	58140	59663	62112	-0.442025	-0.000146	307623
31237	Biopsy or removal of nasal polyp or tissue using an endoscope	689656.89	700877.99	831306.55	817973.23	945048.46	0.943382	0.000014	3984863.12	4011	3961	4811	4766	5431	0.935600	0.002402	22980

A = sklearn.linear_model.LinearRegression()
B = A.fit(np.arange(2015,2019+1).reshape(-1,1), df_bill_wide.loc["14060"][("Total Number of Services","Annual")].values.reshape(-1,1) )

B.coef_, B.intercept_

(array([[-334.8]]), array([688684.4]))

import statsmodels.api as sm

df_bill_wide[(category,"Annual")].apply(calc_slope,axis=1, result_type="expand", args=(np.arange(2015,2019+1),) )

X = sm.add_constant(np.arange(2015,2019+1)) # adding a constant
Y = df_bill_wide.loc["14060"][("Total Number of Services","Annual")].values.reshape(-1,1)

model = sm.OLS(Y, X).fit()
predictions = model.predict(X) 

print_model = model.summary()

/Users/ryerrabelli/.conda/envs/GeospatialAnalysis/lib/python3.8/site-packages/statsmodels/stats/stattools.py:74: ValueWarning: omni_normtest is not valid with less than 8 observations; 5 samples were given.
  warn("omni_normtest is not valid with less than 8 observations; %i "

print_model

OLS Regression Results

Dep. Variable:	y	R-squared:	0.867
Model:	OLS	Adj. R-squared:	0.823
Method:	Least Squares	F-statistic:	19.59
Date:	Wed, 22 Jun 2022	Prob (F-statistic):	0.0214
Time:	17:45:16	Log-Likelihood:	-33.205
No. Observations:	5	AIC:	70.41
Df Residuals:	3	BIC:	69.63
Df Model:	1
Covariance Type:	nonrobust

	coef	std err	t	P>|t|	[0.025	0.975]
const	6.887e+05	1.53e+05	4.514	0.020	2.03e+05	1.17e+06
x1	-334.8000	75.648	-4.426	0.021	-575.546	-94.054

Omnibus:	nan	Durbin-Watson:	3.135
Prob(Omnibus):	nan	Jarque-Bera (JB):	0.361
Skew:	0.535	Prob(JB):	0.835
Kurtosis:	2.232	Cond. No.	2.88e+06

Notes:
[1] Standard Errors assume that the covariance matrix of the errors is correctly specified.
[2] The condition number is large, 2.88e+06. This might indicate that there are
strong multicollinearity or other numerical problems.

Data

with pd.option_context('display.float_format', '{:,.2f}'.format):
    display(df_bill_wide)

	Category	Total Medicare Payment Amount										Total Number of Services
	Stat						Annual										Annual
	Year	Intercept	P	Pearson Coef	Slope	Sum	2015	2016	2017	2018	2019	Intercept	P	Pearson Coef	Slope	Sum	2015	2016	2017	2018	2019
HCPCS Code	HCPCS Description
31575	Diagnostic examination of voice box using flexible endoscope	451,424,416.13	0.14	-0.76	-222,163.94	16,598,776.03	4,095,655.56	3,367,114.38	2,963,951.91	3,007,322.23	3,164,731.95	2,769,347.10	0.46	-0.44	-1,342.50	307623	69604	58104	58140	59663	62112
31231	Diagnostic examination of nasal passages using an endoscope	-172,279,910.86	0.09	0.81	86,219.34	8,122,459.37	1,561,172.64	1,473,524.08	1,495,479.72	1,726,503.13	1,865,779.80	-4,792,858.60	0.06	0.87	2,394.00	179197	33396	32245	32767	38601	42188
14060	Tissue transfer repair of wound (10 sq centimeters or less) of eyelids, nose, ears, and/or lips	319,500,137.19	0.24	-0.64	-154,894.60	35,388,675.22	7,253,164.21	7,562,395.26	6,718,042.68	7,190,368.43	6,664,704.64	688,684.40	0.02	-0.93	-334.80	66964	13950	14043	13157	13033	12781
30140	Removal of nasal air passage	312,698,414.27	0.44	-0.46	-153,031.11	20,173,276.59	4,090,571.97	4,163,402.15	4,804,703.77	3,414,962.46	3,699,636.24	-2,217,309.20	0.00	0.99	1,105.40	61413	9942	11063	12663	13489	14256
31579	Examination to assess movement of vocal cord flaps using an endoscope	-61,589,349.41	0.67	0.26	31,129.92	5,998,496.16	1,329,457.32	1,080,066.04	957,141.66	1,213,382.40	1,418,448.74	-1,836,075.30	0.18	0.71	916.30	60509	11995	9957	10470	13064	15023
30520	Reshaping of nasal cartilage	-634,251,278.21	0.05	0.88	316,496.23	20,608,078.41	3,822,391.75	3,510,467.18	3,887,689.27	4,454,847.45	4,932,682.76	-728,375.70	0.02	0.93	366.10	50240	9409	9508	10270	10119	10934
11042	Removal of skin and tissue first 20 sq cm or less	-198,439,489.63	0.47	0.43	98,803.65	4,237,372.20	509,299.24	600,263.11	1,165,644.85	1,317,431.90	644,733.10	-1,187,471.50	0.69	0.25	593.30	46073	5179	8266	14608	11483	6537
31267	Removal of nasal sinus tissue using an endoscope	-1,368,929,056.95	0.02	0.92	680,864.21	21,870,283.20	2,454,520.71	4,313,264.94	4,546,272.86	5,081,500.91	5,474,723.78	-1,193,842.30	0.00	0.98	596.30	44474	7561	8394	9166	9227	10126
14040	Tissue transfer repair of wound (10 sq centimeters or less) of the forehead, cheeks, chin, mouth, neck, underarms, genitals, hands, and/or feet	330,869,453.06	0.03	-0.92	-162,170.07	18,862,149.70	4,175,118.98	3,907,715.69	3,725,634.72	3,471,107.63	3,582,572.68	856,496.80	0.03	-0.91	-421.00	36699	8379	7635	7356	6475	6854
15260	Relocation of patient skin to nose, ears, eyelids, and/or lips (20 sq centimeters or less)	205,300,825.92	0.38	-0.51	-99,852.42	19,492,504.15	3,816,163.30	4,358,891.55	3,827,316.88	3,987,570.86	3,502,561.56	512,061.20	0.05	-0.89	-250.40	35022	7255	7518	7164	6646	6439
31525	Diagnostic examination of voice box using an endoscope	-338,018,806.51	0.02	0.94	168,013.77	4,324,807.64	548,628.32	783,501.65	700,741.37	1,022,976.64	1,268,959.66	-2,470,751.40	0.02	0.94	1,228.20	32640	4231	5918	5299	7722	9470
31276	Exploration of nasal sinus using an endoscope	1,132,877,449.20	0.20	-0.69	-560,129.95	15,476,690.17	3,551,553.36	3,919,275.81	4,510,942.92	1,568,753.14	1,926,164.94	1,431,661.60	0.21	-0.68	-707.20	26196	5855	6240	7061	3202	3838
14301	Tissue transfer repair of wound (30.1 to 60.0 sq centimeters)	-590,784,864.70	0.01	0.95	294,810.79	19,242,463.38	3,422,400.08	3,325,471.33	3,811,793.74	4,247,217.10	4,435,581.13	-406,827.80	0.00	0.98	204.00	23201	4204	4516	4544	4910	5027
31237	Biopsy or removal of nasal polyp or tissue using an endoscope	-125,846,096.62	0.02	0.94	62,787.84	3,984,863.12	689,656.89	700,877.99	831,306.55	817,973.23	945,048.46	-730,600.50	0.02	0.94	364.50	22980	4011	3961	4811	4766	5431
14041	Tissue transfer repair of wound (10.1 to 30.0 sq centimeters) of the forehead, cheeks, chin, mouth, neck, underarms, genitals, hands, and/or feet	-41,271,689.79	0.70	0.24	21,631.67	11,796,912.76	2,481,173.43	2,174,377.77	2,237,142.30	2,455,397.22	2,448,822.04	7,415.00	0.99	-0.01	-1.60	20939	4486	3871	4066	4205	4311
69436	Incision of eardrum with insertion of eardrum tube under general anesthesia	213,243,398.28	0.19	-0.70	-105,139.91	5,880,968.80	1,291,093.18	1,541,750.79	989,017.79	1,045,676.06	1,013,430.98	163,832.00	0.19	-0.69	-79.20	20428	4371	4125	3889	4011	4032
15004	Preparation of graft site of face, scalp, eyelids, mouth, neck, ears, eye region, genitals, hands, feet, and/or multiple fingers or toes (first 100 sq cm or 1% body area of infants and children)	-74,622,094.70	0.01	0.97	37,284.98	2,908,600.22	492,031.70	549,173.13	607,020.35	614,663.70	645,711.34	-240,811.60	0.16	0.74	121.40	20261	3634	3988	4310	4188	4141
31256	Incision of nasal sinus using an endoscope	37,352,908.96	0.38	-0.51	-18,021.06	5,022,144.63	1,021,363.93	1,035,079.99	1,059,938.69	913,926.80	991,835.22	179,705.90	0.41	-0.48	-87.30	18109	3599	3847	3956	3242	3465
11642	Removal of malignant growth (1.1 to 2.0 centimeters) of the face, ears, eyelids, nose, or lips	36,967,796.20	0.02	-0.93	-18,209.01	1,201,135.31	289,427.10	245,483.24	238,474.17	213,254.24	214,496.56	468,070.00	0.02	-0.94	-230.60	14749	3552	3055	2923	2585	2634
69930	Implantation of cochlear device	-2,863,306,906.72	0.00	0.98	1,422,857.23	32,980,620.87	4,164,557.97	4,694,007.58	6,277,113.34	8,438,188.15	9,406,753.83	-576,900.90	0.00	0.98	287.10	10899	1626	1818	2191	2587	2677
60500	Removal or exploration of parathyroid glands	-384,862,832.92	0.06	0.86	191,637.10	8,346,019.17	1,466,292.92	1,408,770.07	1,410,847.34	1,862,490.74	2,197,618.10	-354,900.20	0.10	0.80	177.00	10544	1960	1870	1798	2272	2644
31571	Injection of vocal cords using an endoscope with operating microscope or telescope	1,664,074.51	0.97	-0.03	-648.22	1,783,053.68	337,017.46	394,075.85	319,825.53	402,641.13	329,493.71	41,240.90	0.75	-0.20	-19.50	9547	1894	2112	1695	1987	1859
31622	Diagnostic examination of lung airways using an endoscope	10,377,666.75	0.03	-0.90	-5,055.91	899,461.24	185,955.57	191,174.40	178,972.39	174,191.34	169,167.54	155,343.70	0.02	-0.94	-76.10	9250	2037	1907	1836	1720	1750
31541	Removal of growth of tongue and/or vocal cord stripping using an endoscope with operating microscope or telescope	24,238,302.44	0.51	-0.40	-11,841.86	1,766,354.11	428,916.69	311,467.78	318,524.17	364,008.38	343,437.09	92,173.20	0.37	-0.52	-45.00	7041	1635	1317	1301	1439	1349
11643	Removal of malignant growth (2.1 to 3.0 centimeters) of the face, ears, eyelids, nose, or lips	16,925,561.91	0.05	-0.88	-8,327.74	642,551.63	139,924.42	140,722.56	128,258.93	129,997.44	103,648.28	135,124.70	0.07	-0.84	-66.30	6988	1487	1479	1414	1426	1182
69801	Incision of fluid canals of inner ear with infusion of drugs, transcanal approach	21,659,579.29	0.08	-0.84	-10,679.57	594,412.84	149,143.78	125,889.28	101,842.11	117,694.22	99,843.45	224,419.00	0.07	-0.85	-110.60	6694	1628	1419	1184	1357	1106
31536	Biopsy of voice box using an endoscope with operating microscope or telescope	402,826.01	0.99	-0.01	-68.77	1,320,604.79	286,775.12	252,209.73	235,033.38	268,100.83	278,485.73	10,221.90	0.87	-0.11	-4.50	5727	1235	1093	1071	1138	1190
69433	Incision of eardrum with insertion of eardrum tube under local or topical anesthesia	2,082,486.27	0.88	-0.10	-975.09	578,678.96	137,111.76	95,794.52	105,930.47	119,417.25	120,424.96	21,645.00	0.86	-0.11	-10.20	5358	1272	885	987	1101	1113
30930	Therapeutic fracture of nasal passages	-8,543,396.98	0.58	0.34	4,366.51	1,319,228.10	266,267.33	230,010.14	285,853.59	267,984.22	269,112.82	-59,856.00	0.18	0.71	30.20	5287	1052	953	1057	1091	1134
43200	Diagnostic examination of esophagus using an endoscope	15,438,590.04	0.01	-0.95	-7,621.84	326,653.46	78,306.83	75,608.99	63,212.10	63,046.87	46,478.67	353,421.90	0.00	-1.00	-174.70	5260	1430	1192	1037	897	704
21235	Obtaining ear cartilage for grafting	-160,906,618.84	0.04	0.89	80,002.89	2,296,051.46	268,245.90	348,416.66	542,354.62	589,131.20	547,903.08	23,230.20	0.70	-0.24	-11.00	5216	979	1155	1076	1009	997
31288	Removal of nasal sinus tissue using an endoscope	209,977,666.34	0.32	-0.57	-103,889.11	2,166,667.44	460,441.25	585,987.47	820,141.39	132,215.78	167,881.55	692,232.10	0.12	-0.78	-342.70	5031	1395	1421	1705	236	274
31254	Partial removal of nasal sinus using an endoscope	-109,587,389.11	0.10	0.81	54,556.94	2,269,834.67	278,635.35	433,153.49	514,211.15	551,675.73	492,158.95	-100,084.70	0.05	0.88	50.10	4835	891	890	927	1081	1046
31535	Biopsy of voice box using an endoscope	13,020,560.75	0.52	-0.39	-6,362.61	935,851.67	181,200.03	232,824.57	168,478.11	175,099.42	178,249.54	57,413.60	0.22	-0.67	-28.00	4688	963	1039	881	925	880
20926	Tissue graft	-83,870,910.20	0.10	0.80	41,842.81	2,630,208.04	426,939.03	450,643.34	585,782.18	610,737.46	556,106.03	-92,062.10	0.09	0.83	46.10	4608	797	865	1009	954	983
43191	Diagnostic examination of esophagus using an endoscope	-18,405,794.32	0.01	0.97	9,161.54	365,159.32	54,382.61	67,967.41	68,026.51	81,217.55	93,565.24	-26,194.60	0.58	0.34	13.40	4166	777	909	770	823	887
30130	Removal of nasal air passage	29,693,651.65	0.09	-0.83	-14,619.98	1,025,739.76	227,895.47	229,411.98	216,179.41	165,502.70	186,750.20	92,643.20	0.11	-0.79	-45.60	3340	717	762	717	548	596
30465	Widening of nasal passage	-156,245,158.57	0.10	0.81	77,759.32	2,976,999.79	423,255.25	460,478.90	747,604.40	606,739.83	738,921.41	-166,148.70	0.06	0.86	82.70	3286	483	514	792	687	810
30802	Destruction of soft tissue in nasal passages	915,438.12	0.97	-0.02	-402.28	520,146.37	93,891.34	144,814.17	68,066.24	98,175.24	115,199.38	-73,211.00	0.27	0.62	36.60	3056	553	609	491	725	678
60240	Removal of thyroid	143,371,216.45	0.09	-0.82	-70,876.74	2,064,149.25	559,710.52	564,614.42	289,389.15	325,602.27	324,832.89	184,122.60	0.10	-0.81	-91.00	2878	746	793	423	457	459
31253	Complete examination of nose and sinuses using an endoscope	-522,371,533.22	0.05	0.87	259,154.08	1,711,281.13	0.00	0.00	0.00	831,021.41	880,259.72	-776,034.00	0.06	0.87	385.00	2555	0	0	0	1260	1295
31259	Removal of tissue from sphenoid sinus using an endoscope	-415,916,300.11	0.04	0.89	206,335.51	1,312,137.99	0.00	0.00	0.00	560,920.86	751,217.13	-754,285.60	0.04	0.89	374.20	2379	0	0	0	1016	1363
15120	Skin graft of face, scalp, eyelids, mouth, neck, ears, eye region, genitals, hands, feet, and/or multiple fingers or toes (first 100 sq cm or less, or 1% body area of infants and children)	-73,318,856.05	0.11	0.79	36,521.74	1,727,467.67	249,466.87	333,222.62	322,363.94	447,454.72	374,959.52	-38,254.00	0.32	0.57	19.20	2362	392	498	466	538	468
31238	Control of nasal bleeding using an endoscope	17,320,326.24	0.18	-0.71	-8,551.92	355,548.25	103,707.75	65,452.13	57,244.23	70,939.82	58,204.32	78,079.70	0.25	-0.63	-38.50	2126	581	379	360	456	350
31526	Diagnostic examination of voice box using an endoscope with operating microscope or telescope	5,827,781.05	0.35	-0.53	-2,869.05	204,525.91	49,005.12	34,247.57	47,429.81	44,119.52	29,723.89	61,335.40	0.25	-0.64	-30.20	2110	507	369	480	427	327
60220	Total removal of thyroid lobe on one side of the neck	-46,324,502.37	0.02	0.94	23,079.72	1,136,494.62	168,535.09	208,492.40	242,281.69	258,011.07	259,174.37	-45,834.90	0.09	0.82	22.90	1772	293	323	394	386	376
31257	Complete examination of nose and sinuses and removal of nasal sinus using an endoscope	-308,640,222.79	0.04	0.89	153,117.15	985,363.99	0.00	0.00	0.00	439,556.46	545,807.53	-537,793.80	0.04	0.89	266.80	1709	0	0	0	750	959
42415	Partial removal of salivary gland growth with release of facial nerve	26,616,259.72	0.56	-0.35	-13,081.76	1,151,738.89	196,315.23	287,774.38	301,095.61	183,520.11	183,033.56	30,579.80	0.58	-0.33	-15.00	1624	273	406	396	296	253
38510	Biopsy or removal of lymph nodes of neck, open procedure	18,177,988.93	0.25	-0.64	-8,957.26	555,987.63	131,596.26	124,915.20	113,084.72	74,247.77	112,143.68	12,616.90	0.76	-0.19	-6.10	1566	325	331	339	222	349
31240	Removal of nasal breathing passages using an endoscope	-18,103,251.99	0.16	0.73	9,002.31	272,076.72	53,348.63	27,758.99	47,718.67	62,021.47	81,228.96	-147,558.10	0.01	0.96	73.30	1440	180	176	280	339	465
69631	Repair of eardrum and ear canal with opening to ear bones	-15,821,526.05	0.46	0.44	7,955.82	1,126,824.53	205,385.67	220,079.14	249,926.63	192,457.49	258,975.60	-14,281.60	0.65	0.28	7.20	1204	216	234	290	190	274
15730	Creation of flap graft to midface	-248,745,467.50	0.04	0.89	123,401.01	771,838.24	0.00	0.00	0.00	309,666.39	462,171.85	-263,255.40	0.04	0.89	130.60	824	0	0	0	342	482
42826	Removal of tonsils patient age 12 or over	1,370.22	1.00	-0.00	-0.00	6,851.10	0.00	0.00	6,851.10	0.00	0.00	2.40	1.00	-0.00	-0.00	12	0	0	12	0	0

print("Total amount of services")
A1 = np.sum(df_bill_wide[("Total Number of Services","Total")])
print("{:,}".format(A1))
print("{:,}".format(A1/5))
print("Total medical payment")
A2 = np.sum(df_bill_wide[("Total Medicare Payment Amount","Total")].astype(np.int64) )
print("{:,}".format(A2))
print("{:,}".format(A2/5))

Total amount of services
1,233,099
246,619.8
Total medical payment
320,317,215
64,063,443.0

pd.columns = pd.MultiIndex.from_tuples([tuple(col.split(":")) if ":" in col else (col,"") for col in df_bill_wide.columns])

[('Total Number of Services', ''),
 ('Total Medicare Payment Amount', ''),
 ('Total Number of Services', ' 2019'),
 ('Total Medicare Payment Amount', ' 2019'),
 ('Total Number of Services', ' 2018'),
 ('Total Medicare Payment Amount', ' 2018'),
 ('Total Number of Services', ' 2017'),
 ('Total Medicare Payment Amount', ' 2017'),
 ('Total Number of Services', ' 2016'),
 ('Total Medicare Payment Amount', ' 2016'),
 ('Total Number of Services', ' 2015'),
 ('Total Medicare Payment Amount', ' 2015'),
 ('Total Medicare Payment Amount', ' Slope'),
 ('Total Medicare Payment Amount', ' Pearson Coef'),
 ('Total Number of Services', ' Slope'),
 ('Total Number of Services', ' Pearson Coef')]

pd.MultiIndex.from_tuples([("Gasoline", "Toyoto"), 
                                  ("Gasoline", "Ford"), 
                                  ("Electric", "Tesla"),
                                  ("Electric", "Nio")])

fips2county = pd.read_csv("data/fips2county.tsv", sep="\t", comment='#', dtype=str)

# The ent CSV file only contains the counties which are analyzable
df_loc_orig = pd.read_csv("data/2022_04_10 ent initial output.csv", dtype={"FIPS": str})
# Merge with the fips 2 county standard data set
df_loc_wide = pd.merge(left=df_loc_orig, right=fips2county, how="left", left_on='FIPS', right_on='CountyFIPS')
# Insert a county "County, ST" col (i.e. "Freehold, NJ" or "Chicago, IL") for ease
df_loc_wide.insert(1, "County_St", df_loc_wide["CountyName"].astype(str) + ", " + df_loc_wide["StateAbbr"].astype(str))
# Display with all the columns
with pd.option_context('display.max_rows', 3, 'display.max_columns', None): 
    display(df_loc_wide)
    pass

loc_simple = ["FIPS", "CountyName","StateAbbr", "% ASC Billing", "Moran I score for ACS billing fraction"]
df_loc_wide_simple=df_loc_wide[loc_simple]

loc_main = ["FIPS", "County",	"StateFIPS", "Total Medicare Payment Amount", "% ASC Procedures", "% ASC Billing",	"CountyFIPS_3",	"CountyName",	"StateName",	"CountyFIPS",	"StateAbbr",	"STATE_COUNTY"]
#a=pd.merge(right=df_loc_orig, left=fips2county, how="outer", right_on='FIPS', left_on='CountyFIPS')
#a=a.loc[:,loc_main]
#df_loc_orig2=df_loc_orig.loc[:,["FIPS","pop","Moran I score for ACS billing fraction","County"]]

	FIPS	County_St	Total Number of Services	Total Medicare Payment Amount	Total Number of Services: 2019	Total Medicare Payment Amount: 2019	Total Number of Services: 2018	Total Medicare Payment Amount: 2018	Total Number of Services: 2017	Total Medicare Payment Amount: 2017	Total Number of Services: 2016	Total Medicare Payment Amount: 2016	Total Number of Services: 2015	Total Medicare Payment Amount: 2015	tot_ratio	% ASC Procedures: 2019	% ASC Billing: 2019	% ASC Procedures: 2018	% ASC Billing: 2018	% ASC Procedures: 2017	% ASC Billing: 2017	% ASC Procedures: 2016	% ASC Billing: 2016	% ASC Procedures: 2015	% ASC Billing: 2015	% ASC Procedures	% ASC Billing	Beneficiaries with Part A and Part B	Average Age	Percent Male	Percent Non-Hispanic White	Percent African American	Percent Hispanic	Percent Eligible for Medicaid	Average HCC Score	Hospital Readmission Rate	Emergency Department Visits per 1000 Beneficiaries	Procedures Per Capita Standardized Costs	Procedure Events Per 1000 Beneficiaries	metro	pct_poverty	median_house_income	pop	2013_Rural_urban_cont_code	Pct_wthout_high_diploma	Pct_wth_high_diploma	Pct_wth_some_coll	Pct_wth_coll_degree	unemployment	pct_uninsured	fibro	tabacco	obesity	migrane	Alzheimers	Depression	Alcohol Abuse	Drug Abuse	Schizo_othr_psych	COPD	Chronic Kidney Disease	Osteoporosis	Stroke	Diabetes	Asthma	Arthritis	Hypertension	Heart Failure	Ischemic Heart Disease	Population Density	Medicare Population Density	Moran I score for ACS billing fraction	County	StateFIPS	CountyFIPS_3	CountyName	StateName	CountyFIPS	StateAbbr	STATE_COUNTY
0	01017	Chambers, AL	408.0	30064.800000	157.0	10363.09	115.0	6360.510000	136.0	13341.20	0.0	0.00	0.0	0.00	14.196990	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.000000	0.0	0.0	0.000000	0.000000	8489.0	69.2	44.126	64.466	34.326	0.286	28.144	1.04	16.296	726.4	502.664	3765.2	0	19.76	38781.0	33660.4	6.0	18.4	36.7	31.6	13.3	4.52	11.94	23.6	11.0	22.2	2.8	11.06	13.92	2.125	3.5	3.30	14.46	25.76	4.40	3.54	34.70	4.10	35.56	67.0	16.78	31.58	56.426908	14.230610	Non Significant	Chambers	01	017	Chambers	Alabama	01017	AL	AL | CHAMBERS
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
940	51041	Chesterfield, VA	617.0	204885.309999	111.0	47135.49	167.0	54715.569999	187.0	53982.17	78.0	24873.24	74.0	24178.84	34.339788	28.828829	51.859904	47.305389	61.493685	41.176471	61.207895	15.384615	20.982791	0.0	0.0	32.414911	47.027037	52776.6	72.0	44.492	79.032	15.324	1.498	8.708	0.89	16.836	597.8	663.378	5401.4	1	6.96	78863.0	343550.6	1.0	7.7	23.6	28.3	40.5	3.42	8.98	17.6	6.8	20.0	2.8	9.76	16.92	1.550	1.6	2.82	9.02	20.84	5.48	3.50	26.98	5.34	31.40	59.0	10.50	25.18	811.606508	124.679835	Non Significant	Chesterfield	51	041	Chesterfield	Virginia	51041	VA	VA | CHESTERFIELD

941 rows × 80 columns

Convert wide df to long df - i.e. separate out the year columns into different rows

col_categories = ["Total Number of Services:", "Total Medicare Payment Amount:", "% ASC Procedures:", "% ASC Billing:"]
cols_to_keep = ["FIPS","County_St"]  # columns to keep in every subgroup so you can line up extra info later

# Create list of df's to combine later, each df is from melting of one category of columns
df_loc_longs = []

# Convert each type of category to long format in separate dataframes
for col_category in col_categories:
        df_loc_long = df_loc_wide.melt(id_vars=cols_to_keep, 
                               var_name="Year", 
                               value_vars=[f"{col_category} {year}" for year in range(2015, 2019 +1)], 
                               value_name=f"{col_category} in Year",
                               )
        df_loc_long["Year"] = df_loc_long["Year"].replace({ f"{col_category} {year}":f"{year}" for year in range(2015, 2019 +1)})
        df_loc_longs.append(df_loc_long)

# Merge the separate category dataframes
df_loc_long = df_loc_longs[0]
for ind in range(1,len(df_loc_longs)):
    df_loc_long = pd.merge(left=df_loc_long, right=df_loc_longs[ind], how="outer", on=(cols_to_keep+["Year"]) )

# Merge with the overall wide dataframe to keep those other values
df_loc_long = pd.merge(left=df_loc_long, 
                   right=df_loc_wide.drop([f"{col_category} {year}" for year in range(2015, 2019 +1) for col_category in col_categories], axis=1), 
                   how="left", on=cols_to_keep)

display(df_loc_long)

	FIPS	County_St	Year	Total Number of Services: in Year	Total Medicare Payment Amount: in Year	% ASC Procedures: in Year	% ASC Billing: in Year	Total Number of Services	Total Medicare Payment Amount	tot_ratio	...	Medicare Population Density	Moran I score for ACS billing fraction	County	StateFIPS	CountyFIPS_3	CountyName	StateName	CountyFIPS	StateAbbr	STATE_COUNTY
0	01017	Chambers, AL	2015	0.0	0.00	0.000000	0.000000	408.0	30064.800000	14.196990	...	14.230610	Non Significant	Chambers	01	017	Chambers	Alabama	01017	AL	AL | CHAMBERS
1	01033	Colbert, AL	2015	108.0	10404.39	0.000000	0.000000	272.0	37080.230000	16.000000	...	22.681014	Non Significant	Colbert	01	033	Colbert	Alabama	01033	AL	AL | COLBERT
2	01045	Dale, AL	2015	0.0	0.00	0.000000	0.000000	12.0	405.210000	0.999104	...	17.700437	Non Significant	Dale	01	045	Dale	Alabama	01045	AL	AL | DALE
3	01083	Limestone, AL	2015	0.0	0.00	0.000000	0.000000	55.0	9515.590000	4.000000	...	29.157261	Non Significant	Limestone	01	083	Limestone	Alabama	01083	AL	AL | LIMESTONE
4	05145	White, AR	2015	1217.0	48412.57	0.000000	0.000000	1269.0	52190.220000	11.995594	...	15.224018	Non Significant	White	05	145	White	Arkansas	05145	AR	AR | WHITE
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
4700	21073	Franklin, KY	2019	0.0	0.00	0.000000	0.000000	114.0	7749.960000	3.910144	...	62.858188	Non Significant	Franklin	21	073	Franklin	Kentucky	21073	KY	KY | FRANKLIN
4701	56021	Laramie, WY	2019	422.0	79083.86	100.000000	100.000000	1784.0	337949.890001	21.000000	...	6.286729	Non Significant	Laramie	56	021	Laramie	Wyoming	56021	WY	WY | LARAMIE
4702	54041	Lewis, WV	2019	0.0	0.00	0.000000	0.000000	606.0	26648.230000	4.000000	...	10.524948	Low-Low	Lewis	54	041	Lewis	West Virginia	54041	WV	WV | LEWIS
4703	50027	Windsor, VT	2019	319.0	12093.61	0.000000	0.000000	1132.0	47825.130000	35.000000	...	14.320922	Low-Low	Windsor	50	027	Windsor	Vermont	50027	VT	VT | WINDSOR
4704	51041	Chesterfield, VA	2019	111.0	47135.49	28.828829	51.859904	617.0	204885.309999	34.339788	...	124.679835	Non Significant	Chesterfield	51	041	Chesterfield	Virginia	51041	VA	VA | CHESTERFIELD

4705 rows × 65 columns

Analysis

fig = px.choropleth(df_loc_wide, geojson=counties, locations='FIPS', 
                    color='% ASC Procedures',
                    color_continuous_scale="Viridis",
                    #range_color=(0, 12),
                    scope="usa",
                    #facet_col="Moran I score for ACS billing fraction",
                    labels={
                        "2013_Rural_urban_cont_code":"2013-RUCA",
                        "pop":"Pop.",
                        "Average Age":"Mean Age",
                        "Percent Male":"% M",
                        "tot_ratio":"Tot. Ratio",
                        },
                    hover_name="County_St",
                    hover_data={
                        '% ASC Procedures':   ":.0f",
                        "FIPS":True, 
                        "pop":  ":.1f", 
                        "2013_Rural_urban_cont_code":True,
                        "Average Age":  ":.1f", 
                        "Percent Male": ":.1f",
                        },
                    )

fig.update_layout(
    hoverlabel=dict(
        bgcolor="white",
        font_size=16,
        font_family="Rockwell",
        align="auto"
    )
)
fig.update_layout(legend=dict(
    orientation="h",
    yanchor="bottom",
    y=1.02,
    xanchor="right",
    x=1
))

# Define layout specificities
fig.update_layout(
    margin={"r":0,"t":0,"l":0,"b":0},
    title={
        'text': f"% ASC Procedures",
        'y':0.95,
        'x':0.5,
        'xanchor': 'center',
        'yanchor': 'top'
    }
)
#fig.show()


save_figure(fig,"choropleth-total")

fig = px.choropleth(df_loc_long, geojson=counties, locations='FIPS', 
                    color='% ASC Procedures: in Year',
                    color_continuous_scale="Viridis",
                    #range_color=(0, 12),
                    scope="usa",
                    #facet_col="Moran I score for ACS billing fraction",
                    labels={
                        "2013_Rural_urban_cont_code":"2013-RUCA",
                        "pop":"Pop.",
                        "Average Age":"Mean Age",
                        "Percent Male":"% M",
                        "tot_ratio":"Tot. Ratio",
                        },
                    hover_name="County_St",
                    hover_data={
                        "FIPS":True, 
                        "pop":  ":.1f", 
                        "2013_Rural_urban_cont_code":True,
                        "Average Age":  ":.1f", 
                        "Percent Male": ":.1f",
                        },
                    animation_frame="Year",
                    )

fig.update_layout(
    hoverlabel=dict(
        bgcolor="white",
        font_size=16,
        font_family="Rockwell",
        align="auto"
    )
)
fig.update_layout(legend=dict(
    orientation="h",
    yanchor="bottom",
    y=1.02,
    xanchor="right",
    x=1
))

# Define layout specificities
fig.update_layout(
    margin={"r":0,"t":0,"l":0,"b":0},
    title={
        'text': f"% ASC Procedures by Year",
        'y':0.95,
        'x':0.5,
        'xanchor': 'center',
        'yanchor': 'top'
    }
)
fig.show()


save_figure(fig,"choropleth-all", animated=True)

Moran's

!pip install rioxarray --quiet

     |████████████████████████████████| 47 kB 2.1 MB/s 
  Installing build dependencies ... done
  Getting requirements to build wheel ... done
    Preparing wheel metadata ... done
  Building wheel for rioxarray (PEP 517) ... done

!pip install pysal --quiet

Looking in indexes: https://pypi.org/simple, https://us-python.pkg.dev/colab-wheels/public/simple/
Collecting pysal
  Downloading pysal-2.6.0-py3-none-any.whl (17 kB)
Collecting pointpats>=2.2.0
  Downloading pointpats-2.2.0.tar.gz (55 kB)
     |████████████████████████████████| 55 kB 2.2 MB/s 
Collecting esda>=2.4.1
  Downloading esda-2.4.1.tar.gz (95 kB)
     |████████████████████████████████| 95 kB 3.0 MB/s 
Collecting spreg>=1.2.4
  Downloading spreg-1.2.4-py3-none-any.whl (209 kB)
     |████████████████████████████████| 209 kB 44.8 MB/s 
Collecting spvcm>=0.3.0
  Downloading spvcm-0.3.0.tar.gz (5.7 MB)
     |████████████████████████████████| 5.7 MB 26.1 MB/s 
Collecting spaghetti>=1.6.5
  Downloading spaghetti-1.6.5-py3-none-any.whl (46 kB)
     |████████████████████████████████| 46 kB 4.6 MB/s 
Collecting giddy>=2.3.3
  Downloading giddy-2.3.3-py3-none-any.whl (60 kB)
     |████████████████████████████████| 60 kB 8.1 MB/s 
Requirement already satisfied: mapclassify>=2.4.3 in /usr/local/lib/python3.7/dist-packages (from pysal) (2.4.3)
Collecting libpysal>=4.6.0
  Downloading libpysal-4.6.2-py3-none-any.whl (2.4 MB)
     |████████████████████████████████| 2.4 MB 29.8 MB/s 
Collecting momepy>=0.5.2
  Downloading momepy-0.5.3-py3-none-any.whl (243 kB)
     |████████████████████████████████| 243 kB 72.9 MB/s 
Collecting segregation>=2.1.0
  Downloading segregation-2.3.1-py3-none-any.whl (147 kB)
     |████████████████████████████████| 147 kB 58.9 MB/s 
Collecting spglm>=1.0.8
  Downloading spglm-1.0.8.tar.gz (37 kB)
Collecting tobler>=0.8.2
  Downloading tobler-0.9.0-py3-none-any.whl (22 kB)
Collecting spopt>=0.2.1
  Downloading spopt-0.4.1-py3-none-any.whl (84 kB)
     |████████████████████████████████| 84 kB 4.3 MB/s 
Collecting mgwr>=2.1.2
  Downloading mgwr-2.1.2.tar.gz (41 kB)
     |████████████████████████████████| 41 kB 697 kB/s 
Collecting inequality>=1.0.0
  Downloading inequality-1.0.0.tar.gz (11 kB)
Collecting splot>=1.1.4
  Downloading splot-1.1.5.post1-py3-none-any.whl (39 kB)
Collecting access>=1.1.3
  Downloading access-1.1.8-py3-none-any.whl (21 kB)
Collecting spint>=1.0.7
  Downloading spint-1.0.7.tar.gz (28 kB)
Requirement already satisfied: pandas>=0.23.4 in /usr/local/lib/python3.7/dist-packages (from access>=1.1.3->pysal) (1.3.5)
Requirement already satisfied: numpy>=1.3 in /usr/local/lib/python3.7/dist-packages (from access>=1.1.3->pysal) (1.21.6)
Requirement already satisfied: geopandas in /usr/local/lib/python3.7/dist-packages (from access>=1.1.3->pysal) (0.10.2)
Requirement already satisfied: requests>=2 in /usr/local/lib/python3.7/dist-packages (from access>=1.1.3->pysal) (2.23.0)
Requirement already satisfied: scipy>=0.11 in /usr/local/lib/python3.7/dist-packages (from esda>=2.4.1->pysal) (1.4.1)
Requirement already satisfied: scikit-learn in /usr/local/lib/python3.7/dist-packages (from esda>=2.4.1->pysal) (1.0.2)
Collecting quantecon>=0.4.7
  Downloading quantecon-0.5.3-py3-none-any.whl (179 kB)
     |████████████████████████████████| 179 kB 64.8 MB/s 
Requirement already satisfied: jinja2 in /usr/local/lib/python3.7/dist-packages (from libpysal>=4.6.0->pysal) (2.11.3)
Requirement already satisfied: appdirs in /usr/local/lib/python3.7/dist-packages (from libpysal>=4.6.0->pysal) (1.4.4)
Requirement already satisfied: beautifulsoup4 in /usr/local/lib/python3.7/dist-packages (from libpysal>=4.6.0->pysal) (4.6.3)
Requirement already satisfied: packaging in /usr/local/lib/python3.7/dist-packages (from libpysal>=4.6.0->pysal) (21.3)
Requirement already satisfied: networkx in /usr/local/lib/python3.7/dist-packages (from mapclassify>=2.4.3->pysal) (2.6.3)
Requirement already satisfied: tqdm>=4.27.0 in /usr/local/lib/python3.7/dist-packages (from momepy>=0.5.2->pysal) (4.64.0)
Collecting pygeos
  Downloading pygeos-0.12.0-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (2.1 MB)
     |████████████████████████████████| 2.1 MB 48.9 MB/s 
Requirement already satisfied: fiona>=1.8 in /usr/local/lib/python3.7/dist-packages (from geopandas->access>=1.1.3->pysal) (1.8.21)
Requirement already satisfied: shapely>=1.6 in /usr/local/lib/python3.7/dist-packages (from geopandas->access>=1.1.3->pysal) (1.8.2)
Requirement already satisfied: pyproj>=2.2.0 in /usr/local/lib/python3.7/dist-packages (from geopandas->access>=1.1.3->pysal) (3.2.1)
Requirement already satisfied: six>=1.7 in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas->access>=1.1.3->pysal) (1.15.0)
Requirement already satisfied: click>=4.0 in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas->access>=1.1.3->pysal) (7.1.2)
Requirement already satisfied: click-plugins>=1.0 in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas->access>=1.1.3->pysal) (1.1.1)
Requirement already satisfied: attrs>=17 in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas->access>=1.1.3->pysal) (21.4.0)
Requirement already satisfied: cligj>=0.5 in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas->access>=1.1.3->pysal) (0.7.2)
Requirement already satisfied: munch in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas->access>=1.1.3->pysal) (2.5.0)
Requirement already satisfied: setuptools in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas->access>=1.1.3->pysal) (57.4.0)
Requirement already satisfied: certifi in /usr/local/lib/python3.7/dist-packages (from fiona>=1.8->geopandas->access>=1.1.3->pysal) (2022.6.15)
Requirement already satisfied: pytz>=2017.3 in /usr/local/lib/python3.7/dist-packages (from pandas>=0.23.4->access>=1.1.3->pysal) (2022.1)
Requirement already satisfied: python-dateutil>=2.7.3 in /usr/local/lib/python3.7/dist-packages (from pandas>=0.23.4->access>=1.1.3->pysal) (2.8.2)
Requirement already satisfied: matplotlib in /usr/local/lib/python3.7/dist-packages (from pointpats>=2.2.0->pysal) (3.2.2)
Collecting opencv-contrib-python>=4.2.0
  Downloading opencv_contrib_python-4.6.0.66-cp36-abi3-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (67.1 MB)
     |████████████████████████████████| 67.1 MB 37 kB/s 
Requirement already satisfied: numba in /usr/local/lib/python3.7/dist-packages (from quantecon>=0.4.7->giddy>=2.3.3->pysal) (0.51.2)
Requirement already satisfied: sympy in /usr/local/lib/python3.7/dist-packages (from quantecon>=0.4.7->giddy>=2.3.3->pysal) (1.7.1)
Requirement already satisfied: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in /usr/local/lib/python3.7/dist-packages (from requests>=2->access>=1.1.3->pysal) (1.24.3)
Requirement already satisfied: idna<3,>=2.5 in /usr/local/lib/python3.7/dist-packages (from requests>=2->access>=1.1.3->pysal) (2.10)
Requirement already satisfied: chardet<4,>=3.0.2 in /usr/local/lib/python3.7/dist-packages (from requests>=2->access>=1.1.3->pysal) (3.0.4)
Requirement already satisfied: seaborn in /usr/local/lib/python3.7/dist-packages (from segregation>=2.1.0->pysal) (0.11.2)
Collecting quilt3
  Downloading quilt3-5.0.0-py3-none-any.whl (78 kB)
     |████████████████████████████████| 78 kB 8.1 MB/s 
Collecting rvlib>=0.0.5
  Downloading rvlib-0.0.6.tar.gz (183 kB)
     |████████████████████████████████| 183 kB 73.2 MB/s 
Collecting deprecation
  Downloading deprecation-2.1.0-py2.py3-none-any.whl (11 kB)
Requirement already satisfied: pip in /usr/local/lib/python3.7/dist-packages (from segregation>=2.1.0->pysal) (21.1.3)
Requirement already satisfied: joblib in /usr/local/lib/python3.7/dist-packages (from segregation>=2.1.0->pysal) (1.1.0)
Requirement already satisfied: cffi>=1.0.0 in /usr/local/lib/python3.7/dist-packages (from rvlib>=0.0.5->segregation>=2.1.0->pysal) (1.15.0)
Requirement already satisfied: PyYAML in /usr/local/lib/python3.7/dist-packages (from rvlib>=0.0.5->segregation>=2.1.0->pysal) (3.13)
Requirement already satisfied: pycparser in /usr/local/lib/python3.7/dist-packages (from cffi>=1.0.0->rvlib>=0.0.5->segregation>=2.1.0->pysal) (2.21)
Requirement already satisfied: llvmlite<0.35,>=0.34.0.dev0 in /usr/local/lib/python3.7/dist-packages (from numba->quantecon>=0.4.7->giddy>=2.3.3->pysal) (0.34.0)
Requirement already satisfied: threadpoolctl>=2.0.0 in /usr/local/lib/python3.7/dist-packages (from scikit-learn->esda>=2.4.1->pysal) (3.1.0)
Collecting rtree
  Downloading Rtree-1.0.0-cp37-cp37m-manylinux_2_17_x86_64.manylinux2014_x86_64.whl (1.0 MB)
     |████████████████████████████████| 1.0 MB 1.8 MB/s 
Collecting pulp
  Downloading PuLP-2.6.0-py3-none-any.whl (14.2 MB)
     |████████████████████████████████| 14.2 MB 40.7 MB/s 
Requirement already satisfied: statsmodels in /usr/local/lib/python3.7/dist-packages (from tobler>=0.8.2->pysal) (0.10.2)
Requirement already satisfied: rasterio in /usr/local/lib/python3.7/dist-packages (from tobler>=0.8.2->pysal) (1.2.10)
Collecting rasterstats
  Downloading rasterstats-0.16.0-py3-none-any.whl (16 kB)
Requirement already satisfied: MarkupSafe>=0.23 in /usr/local/lib/python3.7/dist-packages (from jinja2->libpysal>=4.6.0->pysal) (2.0.1)
Requirement already satisfied: kiwisolver>=1.0.1 in /usr/local/lib/python3.7/dist-packages (from matplotlib->pointpats>=2.2.0->pysal) (1.4.3)
Requirement already satisfied: pyparsing!=2.0.4,!=2.1.2,!=2.1.6,>=2.0.1 in /usr/local/lib/python3.7/dist-packages (from matplotlib->pointpats>=2.2.0->pysal) (3.0.9)
Requirement already satisfied: cycler>=0.10 in /usr/local/lib/python3.7/dist-packages (from matplotlib->pointpats>=2.2.0->pysal) (0.11.0)
Requirement already satisfied: typing-extensions in /usr/local/lib/python3.7/dist-packages (from kiwisolver>=1.0.1->matplotlib->pointpats>=2.2.0->pysal) (4.1.1)
Collecting PyYAML
  Downloading PyYAML-6.0-cp37-cp37m-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_12_x86_64.manylinux2010_x86_64.whl (596 kB)
     |████████████████████████████████| 596 kB 51.5 MB/s 
Requirement already satisfied: jsonschema<5,>=3 in /usr/local/lib/python3.7/dist-packages (from quilt3->segregation>=2.1.0->pysal) (4.3.3)
Collecting boto3>=1.10.0
  Downloading boto3-1.24.12-py3-none-any.whl (132 kB)
     |████████████████████████████████| 132 kB 59.1 MB/s 
Collecting jsonlines==1.2.0
  Downloading jsonlines-1.2.0-py2.py3-none-any.whl (7.6 kB)
Collecting requests-futures==1.0.0
  Downloading requests_futures-1.0.0-py2.py3-none-any.whl (7.4 kB)
Requirement already satisfied: tenacity>=5.1.1 in /usr/local/lib/python3.7/dist-packages (from quilt3->segregation>=2.1.0->pysal) (8.0.1)
Collecting aws-requests-auth>=0.4.2
  Downloading aws_requests_auth-0.4.3-py2.py3-none-any.whl (6.8 kB)
Collecting s3transfer<0.7.0,>=0.6.0
  Downloading s3transfer-0.6.0-py3-none-any.whl (79 kB)
     |████████████████████████████████| 79 kB 9.3 MB/s 
Collecting jmespath<2.0.0,>=0.7.1
  Downloading jmespath-1.0.1-py3-none-any.whl (20 kB)
Collecting botocore<1.28.0,>=1.27.12
  Downloading botocore-1.27.12-py3-none-any.whl (8.9 MB)
     |████████████████████████████████| 8.9 MB 40.0 MB/s 
Collecting urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1
  Downloading urllib3-1.25.11-py2.py3-none-any.whl (127 kB)
     |████████████████████████████████| 127 kB 61.4 MB/s 
Requirement already satisfied: pyrsistent!=0.17.0,!=0.17.1,!=0.17.2,>=0.14.0 in /usr/local/lib/python3.7/dist-packages (from jsonschema<5,>=3->quilt3->segregation>=2.1.0->pysal) (0.18.1)
Requirement already satisfied: importlib-metadata in /usr/local/lib/python3.7/dist-packages (from jsonschema<5,>=3->quilt3->segregation>=2.1.0->pysal) (4.11.4)
Requirement already satisfied: importlib-resources>=1.4.0 in /usr/local/lib/python3.7/dist-packages (from jsonschema<5,>=3->quilt3->segregation>=2.1.0->pysal) (5.7.1)
Requirement already satisfied: zipp>=3.1.0 in /usr/local/lib/python3.7/dist-packages (from importlib-resources>=1.4.0->jsonschema<5,>=3->quilt3->segregation>=2.1.0->pysal) (3.8.0)
Requirement already satisfied: snuggs>=1.4.1 in /usr/local/lib/python3.7/dist-packages (from rasterio->tobler>=0.8.2->pysal) (1.4.7)
Requirement already satisfied: affine in /usr/local/lib/python3.7/dist-packages (from rasterio->tobler>=0.8.2->pysal) (2.3.1)
Collecting simplejson
  Downloading simplejson-3.17.6-cp37-cp37m-manylinux_2_5_x86_64.manylinux1_x86_64.manylinux_2_12_x86_64.manylinux2010_x86_64.whl (130 kB)
     |████████████████████████████████| 130 kB 56.2 MB/s 
Requirement already satisfied: patsy>=0.4.0 in /usr/local/lib/python3.7/dist-packages (from statsmodels->tobler>=0.8.2->pysal) (0.5.2)
Requirement already satisfied: mpmath>=0.19 in /usr/local/lib/python3.7/dist-packages (from sympy->quantecon>=0.4.7->giddy>=2.3.3->pysal) (1.2.1)
Building wheels for collected packages: esda, inequality, mgwr, pointpats, rvlib, spglm, spint, spvcm
  Building wheel for esda (setup.py) ... done
  Created wheel for esda: filename=esda-2.4.1-py3-none-any.whl size=117429 sha256=ba29500a60a53a4b1892d4f900db13eb2d005b7c48b8746f285179260cebf936
  Stored in directory: /root/.cache/pip/wheels/ed/67/c5/4558a178be4c410f341c31f71b5aba9c761a40c9d17dbdfddd
  Building wheel for inequality (setup.py) ... done
  Created wheel for inequality: filename=inequality-1.0.0-py3-none-any.whl size=11800 sha256=e15550e0df0067400fd28ac45fbff01787c978077814cd261eab935b1d3e0edf
  Stored in directory: /root/.cache/pip/wheels/a8/81/7a/af8b9e8ea0b034ac1bdd9d68f3a8ceac179eb5418cdb83c851
  Building wheel for mgwr (setup.py) ... done
  Created wheel for mgwr: filename=mgwr-2.1.2-py3-none-any.whl size=46386 sha256=30479c852d4ff6c43157455bcd84bdc77ff4d9b656edd378f36f8139d957c460
  Stored in directory: /root/.cache/pip/wheels/e8/55/57/c1bf993ab0ab4b9885ac1fdf7f9f960397355cf889b77a3e4b
  Building wheel for pointpats (setup.py) ... done
  Created wheel for pointpats: filename=pointpats-2.2.0-py3-none-any.whl size=60835 sha256=7425fc857e0bb8a90620b650b36591674300abf95c4b65901c73340d009b8337
  Stored in directory: /root/.cache/pip/wheels/41/5f/91/08cc68af630fafdc6bed36128abf8e716e062256a8495fdb2d
  Building wheel for rvlib (setup.py) ... done
  Created wheel for rvlib: filename=rvlib-0.0.6-cp37-cp37m-linux_x86_64.whl size=352109 sha256=e01db77d4717e5674b4ecd37d1320eee4c5314ad43b2335c7da504fad436d5ab
  Stored in directory: /root/.cache/pip/wheels/8e/fe/cb/66b78310be88014dfbe1a21391c873a9fe49dca403f7d9f454
  Building wheel for spglm (setup.py) ... done
  Created wheel for spglm: filename=spglm-1.0.8-py3-none-any.whl size=38806 sha256=4957085fa55b92394a72ed434db51fe5b68ba96a89c6dca08ed4230252bba242
  Stored in directory: /root/.cache/pip/wheels/29/79/e9/453e3b0d858bed16e097c6e6bbd75e2b966bfcfb21b12790d0
  Building wheel for spint (setup.py) ... done
  Created wheel for spint: filename=spint-1.0.7-py3-none-any.whl size=31383 sha256=3e2b44c92421142c2f6b8393d3bd7202c8d8e714f8411ef5f2fe44c4acb016a5
  Stored in directory: /root/.cache/pip/wheels/dd/ac/5d/25d69231f3bffa2cb47ce29bb7eac9df9246aa9ba286a03a2d
  Building wheel for spvcm (setup.py) ... done
  Created wheel for spvcm: filename=spvcm-0.3.0-py3-none-any.whl size=5777202 sha256=f32a4a7cc279c9b39313a1aa9529315ca095508ffd2fe98264e7714728ac8af3
  Stored in directory: /root/.cache/pip/wheels/a4/85/1a/d890ef4d2eaca2788e45925fa531bb58a2138734065abdfe08
Successfully built esda inequality mgwr pointpats rvlib spglm spint spvcm
Installing collected packages: urllib3, jmespath, botocore, s3transfer, libpysal, spreg, simplejson, rtree, requests-futures, quantecon, PyYAML, jsonlines, esda, boto3, aws-requests-auth, spglm, spaghetti, rvlib, rasterstats, quilt3, pygeos, pulp, opencv-contrib-python, giddy, deprecation, tobler, spvcm, spopt, splot, spint, segregation, pointpats, momepy, mgwr, inequality, access, pysal
  Attempting uninstall: urllib3
    Found existing installation: urllib3 1.24.3
    Uninstalling urllib3-1.24.3:
      Successfully uninstalled urllib3-1.24.3
  Attempting uninstall: PyYAML
    Found existing installation: PyYAML 3.13
    Uninstalling PyYAML-3.13:
      Successfully uninstalled PyYAML-3.13
  Attempting uninstall: opencv-contrib-python
    Found existing installation: opencv-contrib-python 4.1.2.30
    Uninstalling opencv-contrib-python-4.1.2.30:
      Successfully uninstalled opencv-contrib-python-4.1.2.30
ERROR: pip's dependency resolver does not currently take into account all the packages that are installed. This behaviour is the source of the following dependency conflicts.
datascience 0.10.6 requires folium==0.2.1, but you have folium 0.8.3 which is incompatible.
Successfully installed PyYAML-6.0 access-1.1.8 aws-requests-auth-0.4.3 boto3-1.24.12 botocore-1.27.12 deprecation-2.1.0 esda-2.4.1 giddy-2.3.3 inequality-1.0.0 jmespath-1.0.1 jsonlines-1.2.0 libpysal-4.6.2 mgwr-2.1.2 momepy-0.5.3 opencv-contrib-python-4.6.0.66 pointpats-2.2.0 pulp-2.6.0 pygeos-0.12.0 pysal-2.6.0 quantecon-0.5.3 quilt3-5.0.0 rasterstats-0.16.0 requests-futures-1.0.0 rtree-1.0.0 rvlib-0.0.6 s3transfer-0.6.0 segregation-2.3.1 simplejson-3.17.6 spaghetti-1.6.5 spglm-1.0.8 spint-1.0.7 splot-1.1.5.post1 spopt-0.4.1 spreg-1.2.4 spvcm-0.3.0 tobler-0.9.0 urllib3-1.25.11

import geopandas
import rioxarray                 # Surface data manipulation
import xarray                    # Surface data manipulation
from pysal.explore import esda   # Exploratory Spatial analytics
from pysal.lib import weights    # Spatial weights
import contextily                # Background tiles

/usr/local/lib/python3.7/dist-packages/numba/np/ufunc/parallel.py:363: NumbaWarning: The TBB threading layer requires TBB version 2019.5 or later i.e., TBB_INTERFACE_VERSION >= 11005. Found TBB_INTERFACE_VERSION = 9107. The TBB threading layer is disabled.
  warnings.warn(problem)
/usr/local/lib/python3.7/dist-packages/spaghetti/network.py:36: FutureWarning: The next major release of pysal/spaghetti (2.0.0) will drop support for all ``libpysal.cg`` geometries. This change is a first step in refactoring ``spaghetti`` that is expected to result in dramatically reduced runtimes for network instantiation and operations. Users currently requiring network and point pattern input as ``libpysal.cg`` geometries should prepare for this simply by converting to ``shapely`` geometries.
  warnings.warn(f"{dep_msg}", FutureWarning)

df_loc_wide_simple

countiesgeo = geopandas.read_file("https://raw.githubusercontent.com/plotly/datasets/master/geojson-counties-fips.json")

countiesgeo.head()

	id	GEO_ID	STATE	COUNTY	NAME	LSAD	CENSUSAREA	geometry
0	01001	0500000US01001	01	001	Autauga	County	594.436	POLYGON ((-86.49677 32.34444, -86.71790 32.402...
1	01009	0500000US01009	01	009	Blount	County	644.776	POLYGON ((-86.57780 33.76532, -86.75914 33.840...
2	01017	0500000US01017	01	017	Chambers	County	596.531	POLYGON ((-85.18413 32.87053, -85.12342 32.772...
3	01021	0500000US01021	01	021	Chilton	County	692.854	POLYGON ((-86.51734 33.02057, -86.51596 32.929...
4	01033	0500000US01033	01	033	Colbert	County	592.619	POLYGON ((-88.13999 34.58170, -88.13925 34.587...

countiesgeo.info()

<class 'geopandas.geodataframe.GeoDataFrame'>
RangeIndex: 3221 entries, 0 to 3220
Data columns (total 8 columns):
 #   Column      Non-Null Count  Dtype   
---  ------      --------------  -----   
 0   id          3221 non-null   object  
 1   GEO_ID      3221 non-null   object  
 2   STATE       3221 non-null   object  
 3   COUNTY      3221 non-null   object  
 4   NAME        3221 non-null   object  
 5   LSAD        3221 non-null   object  
 6   CENSUSAREA  3221 non-null   float64 
 7   geometry    3221 non-null   geometry
dtypes: float64(1), geometry(1), object(6)
memory usage: 201.4+ KB

countiesnick = geopandas.read_file("examples/npeterman/ent redo3anova.geojson")

countiesnick[["FIPS","LISA_CL"]]

	FIPS	LISA_CL
0	01017	3
1	01033	0
2	01083	0
3	05145	0
4	06089	0
...	...	...
832	21059	0
833	21073	0
834	56021	0
835	50027	2
836	51041	0

837 rows × 2 columns

# Generate W from the GeoDataFrame
w = weights.distance.KNN.from_dataframe(countiesnick, k=8)
# Row-standardization
w.transform = 'R'

w

<libpysal.weights.distance.KNN at 0x7fc7af8a00d0>